package dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import resource.DB;
import beans.Usuario;

public class UsuarioDAOJDBC implements UsuarioDAO{
	private static UsuarioDAOJDBC instance;
	private Connection conexao;
	private PreparedStatement pStmt;
	private List<Usuario> usuarios;
	
	private UsuarioDAOJDBC() throws SQLException{
		this.conexao = DB.getInstance().conecta();
		this.criarTabela();
	}
	
	public static UsuarioDAOJDBC getInstance() throws SQLException{
		if(instance == null)
			instance = new UsuarioDAOJDBC();
		return instance;
	}
	
	public boolean inserir(Usuario usuario) throws SQLException{
		String sql = 	"INSERT INTO users VALUES " +
						"("+
							"NULL,?,?,?,?,?,?,?,?"+
						")";
		
		this.pStmt = conexao.prepareStatement(sql);
		this.pStmt.setString(1, usuario.getNome());
		this.pStmt.setString(2, usuario.getSenha());
		this.pStmt.setString(3, usuario.getLogin());
		this.pStmt.setString(4, usuario.getEmail());
		this.pStmt.setString(5, usuario.getSexo());
		this.pStmt.setTimestamp(6, new Timestamp(usuario.getNascimento().getTime()));
		this.pStmt.setString(7, usuario.getDescricao());
		this.pStmt.setString(8, usuario.getLocalizacao());
		
		return this.pStmt.execute();
	}
	
	public boolean excluir(int id) throws SQLException{
		String sql = 	"DELETE FROM users WHERE id = ?";
		
		this.pStmt = conexao.prepareStatement(sql);
		this.pStmt.setString(1, ""+id);
		
		return this.pStmt.execute();
	}
	
	public boolean alterar(Usuario usuario) throws SQLException{
		String sql = 	"UPDATE users set " +
						"id = ?, nome = ?, senha = ?, login = ?," +
						"email = ?, sexo = ?, data_nasc = ?," +
						"descricao = ?, localizacao = ? " +
						"where id = ?";
		this.pStmt = conexao.prepareStatement(sql);
		this.pStmt.setString(1, ""+usuario.getId());
		this.pStmt.setString(1, usuario.getNome());
		this.pStmt.setString(2, usuario.getSenha());
		this.pStmt.setString(3, usuario.getLogin());
		this.pStmt.setString(4, usuario.getEmail());
		this.pStmt.setString(5, usuario.getSexo());
		this.pStmt.setDate(6, new Date(usuario.getNascimento().getTime()));
		this.pStmt.setString(7, usuario.getDescricao());
		this.pStmt.setString(8, usuario.getLocalizacao());
		this.pStmt.setString(9, ""+usuario.getId());
		
		return this.pStmt.execute();
	}
	
	public Usuario recuperar(int id) throws SQLException{
		String sql = 	"SELECT * FROM users WHERE id = ?";
		
		this.pStmt = conexao.prepareStatement(sql);
		this.pStmt.setString(1, ""+id);
		
		ResultSet res = pStmt.executeQuery();
		res.first();
		return new Usuario(
				res.getInt("id"),
				res.getString("nome"),
				res.getString("senha"),
				res.getString("login"),
				res.getString("email"),
				res.getString("sexo"),
				res.getDate("data_nasc"),
				res.getString("descricao"),
				res.getString("localizacao")
		);
	}
	
	public Usuario recuperarPorEmail(String email) throws SQLException{
		String sql = 	"SELECT * FROM users WHERE email = email";
		
		this.pStmt = conexao.prepareStatement(sql);
		this.pStmt.setString(1, ""+email);
		
		ResultSet res = pStmt.executeQuery();
		res.first();
		return new Usuario(
				res.getInt("id"),
				res.getString("nome"),
				res.getString("senha"),
				res.getString("login"),
				res.getString("email"),
				res.getString("sexo"),
				res.getDate("data_nasc"),
				res.getString("descricao"),
				res.getString("localizacao")
		);
	}
	
	public Usuario recuperarPorLogin(String login) throws SQLException{
		String sql = 	"SELECT * FROM users WHERE login = ?";
		
		this.pStmt = conexao.prepareStatement(sql);
		this.pStmt.setString(1,login);
		
		ResultSet res = pStmt.executeQuery();
		res.first();
		return new Usuario(
				res.getInt("id"),
				res.getString("nome"),
				res.getString("senha"),
				res.getString("login"),
				res.getString("email"),
				res.getString("sexo"),
				res.getDate("data_nasc"),
				res.getString("descricao"),
				res.getString("localizacao")
		);
	}
	
	public List<Usuario> recuperarTodos() throws SQLException{
		String sql = 	"SELECT * FROM users";
		
		this.pStmt = conexao.prepareStatement(sql);
		
		this.usuarios = new ArrayList<Usuario>();
		
		Usuario tmpUsr;
		
		ResultSet res = pStmt.executeQuery();
		while(res.next()){
			tmpUsr = new Usuario(
					res.getInt("id"),
					res.getString("nome"),
					res.getString("senha"),
					res.getString("login"),
					res.getString("email"),
					res.getString("sexo"),
					res.getDate("data_nasc"),
					res.getString("descricao"),
					res.getString("localizacao")
			);
			this.usuarios.add(tmpUsr);
		}
		
		return usuarios;
	}
		
	public boolean criarTabela() throws SQLException{
		String sql = 	"CREATE TABLE IF NOT EXISTS users ("+
							"id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,"+
							"nome VARCHAR(255) NOT NULL,"+
							"senha VARCHAR(255) NOT NULL,"+
							"login VARCHAR(255) NOT NULL,"+
							"email VARCHAR(255) NOT NULL,"+
							"sexo CHAR NOT NULL,"+
							"data_nasc DATE NULL,"+
							"descricao TEXT NULL,"+
							"localizacao VARCHAR(255) NULL,"+
							"PRIMARY KEY(id),"+
							"UNIQUE INDEX users_login(login)"+
						")";
		
		this.pStmt = this.conexao.prepareStatement(sql);
		if(this.pStmt.executeUpdate(sql)>0)
			return true;
		return false;
				
	}
}
